package com.study.manage.dal;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;

import com.study.manage.domain.BlogBehavior;
import com.study.manage.domain.enumm.BlogBehaviorType;

public interface BlogBehaviorRepository extends PagingAndSortingRepository<BlogBehavior, Long>,
        JpaSpecificationExecutor<BlogBehavior> {

    @Query("select t from BlogBehavior t where to_days(t.createdAt)=to_days(:date) and t.type=:type and t.evaluateId in (:evaluateId)")
    List<BlogBehavior> findByCreatedAtAndType(@Param("date") Date date,@Param("type") BlogBehaviorType type, @Param("evaluateId") List<Long> evaluateIds);

    @Query("select t from BlogBehavior t where to_days(t.createdAt)=to_days(:date) and t.type=:type and t.evaluateId is null")
    BlogBehavior findByCreatedAtAndType(@Param("date") Date date,@Param("type") BlogBehaviorType type);

    @Query("select t from BlogBehavior t where to_days(t.createdAt)=to_days(:date) and t.type=:type and t.evaluateId is not null")
    List<BlogBehavior> findByEvaluateIdNotNullAndCreatedAtAndType(@Param("date") Date date,@Param("type") BlogBehaviorType type);

    @Query("select t.createdAt,sum(t.showed)*3,'帖子浏览数' as type from BlogBehavior t  where t.type=0 and t.evaluateId in (:evaluateIds) "
            + "and to_days(t.createdAt)>=to_days(:beginAt) and to_days(t.createdAt)<=to_days(:endAt) group by to_days(t.createdAt)")
    List<Object[]> countShowByEvaluateIdInGroupByDay(@Param("beginAt") Date beginAt,
            @Param("endAt") Date endAt, @Param("evaluateIds") List<Long> evaluateIds);

    @Query("select t.createdAt,sum(t.opened)*2,'帖子打开数' as type from BlogBehavior t  where t.type=0 and t.evaluateId in (:evaluateIds) "
            + "and to_days(t.createdAt)>=to_days(:beginAt) and to_days(t.createdAt)<=to_days(:endAt) group by to_days(t.createdAt)")
    List<Object[]> countOpendByEvaluateIdInGroupByDay(@Param("beginAt") Date beginAt,
            @Param("endAt") Date endAt, @Param("evaluateIds") List<Long> evaluateIds);

    @Query("select t.createdAt,sum(t.hudong) from BlogBehavior t  where t.type=0 and t.evaluateId in (:evaluateIds) "
            + "and to_days(t.createdAt)>=to_days(:beginAt) and to_days(t.createdAt)<=to_days(:endAt) group by to_days(t.createdAt)")
    List<Object[]> countHudongByEvaluateIdInGroupByDay(@Param("beginAt") Date beginAt,
            @Param("endAt") Date endAt, @Param("evaluateIds") List<Long> evaluateIds);


    @Query(nativeQuery = true,
            value = "select t.created_at,sum(t.hudong),'互动总数' as type from yy_blog_behavior t  where t.typee=0 and t.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)"

                    + " UNION ALL select t.created_at,sum(t.liked),'点赞数量' as type  from yy_blog_behavior t  where t.typee=0 and t.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)"

                    + " UNION ALL select t.created_at,sum(t.graded),'打分数量' as type  from yy_blog_behavior t  where t.typee=0 and t.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)"

                    + " UNION ALL select t.created_at,sum(t.dianping),'点评数量' as type  from yy_blog_behavior t  where t.typee=0 and t.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)"

                    + " UNION ALL select t.created_at,sum(t.taolun),'讨论数量' as type  from yy_blog_behavior t  where t.typee=0 and t.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)"

                    + " UNION ALL select t.created_at,sum(t.voted),'投票数量' as type  from yy_blog_behavior t  where t.typee=0 and t.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)")
    List<Object[]> countHudongByEvaluateIdInGroupByDays(@Param("beginAt") Date beginAt,
            @Param("endAt") Date endAt, @Param("evaluateIds") List<Long> evaluateIds);

}
